[Amazon Athena] SQLのFilter句を使ってスペース区切りで入力された複数検索キーワードによる検索を実現する

データアナリティクス事業本部 インテグレーション部 機械学習チームの貞松です。

今回はSQLのTipsというか、ちょっと込み入った検索を実現するための一例を紹介します。
最終的なSQLクエリを掲載して終わり、でも良いのですが、パッと見少々理解しづらいところもあるので、処理を分解して一つずつ解説します。

実行環境とユースケース

実行環境は以下の通りです。

• Amazon Athena
  • Athena engine version 3
  • AWSマネジメントコンソールのAthena Query Editor上で実行

ユースケースとしてはBIダッシュボードの検索フォームで入力されたスペース区切りの複数検索キーワードをSQLクエリに渡して、検索結果を返すことを想定しています。

実現したいロジックは以下の通りです。

• スペース区切り(半角でも全角でも可)で複数の検索キーワードの入力を受け付ける
• 入力されたキーワード全てが、検索対象の文字列内に含まれる (AND検索)
• 検索キーワードの出現順序は、入力された順序になっていなくても良い
  • 検索キーワードが3つ入力されていたとして、検索対象の文字列内での出現順序が「3番目の検索キーワード」「１番目の検索キーワード」「2番目の検索キーワード」のようになっていても抽出されること
• 検索キーワードが検索対象内で複数回出現しても正しく抽出されること
• 検索キーワード自体が重複していても正しく抽出されること

最終的なSQLクエリ

最終的に作成したSQLクエリは以下の通りです。
実際のユースケースでは <ここにスペース区切りで複数検索キーワードを入れる想定> の部分にBIダッシュボードの検索フォームから渡された文字列が埋め込まれる記述になる想定です。

with search_word AS (
    SELECT replace('<ここにスペース区切りで複数検索キーワードを入れる想定>', '　', ' ') AS search_word
),
split_search_word as (
    select
        word
    from
        search_word
    cross join
        unnest(split(search_word, ' ')) as t(word)
),
search_result as (
    select
        q10,
        case 
            when count(distinct(word)) filter(where q10 LIKE '%' || word || '%') = (select count(distinct(word)) from split_search_word)
            then 0 -- 検索ワードが全てヒットした場合
            else 1 -- 検索ワードが一つでもヒットしなかった場合
        end as match_result
    from
        enquete
    cross join
        split_search_word
    group by
        q10
)
select
    q10
from
    search_result
where
    match_result = 0
;

以下、実行結果の例です。アンケートのフリーテキスト回答欄のデータ列(q10)に対して、検索キーワードとしては「ありがとう 説明　部署 説明」を入力して実行しています(半角・全角スペースを混ぜつつ、キーワード重複もある状態にしました)

検索キーワードの出現順序や出現回数に関わらず、意図通りに抽出できています。

これだけパッと見ても、処理の内容(具体的なロジック)がわかりにくいので、各処理を分解して順番に解説していきます。

各処理の解説

入力された検索キーワードに含まれる全角スペースを半角スペースに変換する処理

これはもうそのままですね。replace関数で全角スペースを半角スペースに変換しています。

これにより、区切り文字のスペースを半角・全角両方に対応させています。

with search_word AS (
    SELECT replace('<ここにスペース区切りで複数検索キーワードを入れる想定>', '　', ' ') AS search_word
),

入力された検索キーワードを分割する処理

split関数で半角スペースを区切り文字として、検索キーワードを分割した後、unnestすることで複数検索キーワードを縦持ちしています。
わざわざ縦持ちにしている理由は後述します。

split_search_word as (
    select
        word
    from
        search_word
    cross join
        unnest(split(search_word, ' ')) as t(word)
),

前述の実行例のように検索キーワードとして「ありがとう 説明　部署 説明」を入力とした場合は以下のような持ち方になります。

検索対象文字列に各検索キーワードが含まれていた件数をカウントすることで全てのキーワードが含まれているか否かを判定する処理

ここが一番の肝であり、複雑な処理になっています。

search_result as (
    select
        q10,
        case 
            when count(distinct(word)) filter(where q10 LIKE '%' || word || '%') = (select count(distinct(word)) from split_search_word)
            then 0 -- 検索ワードが全てヒットした場合
            else 1 -- 検索ワードが一つでもヒットしなかった場合
        end as match_result
    from
        enquete
    cross join
        split_search_word
    group by
        q10
)

具体的なロジックとしては以下のとおりです。

一つ前の処理で検索キーワードを縦持ちにしたテーブルと検索対象列が含まれるテーブルをCROSS JOINすることで、検索対象文字列×検索キーワードのレコードを持つデータセットになります。

これに対して、以下のよう集計、比較を行うことで、検索対象文字列に検索キーワードが全て含まれるか否かを判定して0 or 1のラベル付けをすることができます。

• filter句を使用することで、検索対象文字列に対して、検索キーワードが含まれているレコードだけをカウント
  • count(distinct(word)) filter(where q10 LIKE '%' || word || '%')
• 縦持ちにした検索キーワードの件数をカウントする
  • (select count(distinct(word)) from split_search_word)
• 上記2つのカウントを突き合わせることで、一致すれば全ての検索キーワードが、検索対象文字列のどこかに含まれていることになるので0(検索キーワードが全てヒットした場合のラベル)とし、一致しなければ1(検索キーワードが一つでもヒットしなかった場合のラベル)とする

filter関数を使用することで、条件に一致するレコードだけを対象として集計することができるので、これを活用しているわけです(もちろんcountだけでなく、sumなど他の集計関数でもfilterを使用することができます)

count(distinct(word)) の部分で、 dicstinct(word) しているのは、検索キーワードが重複している場合の対策です。

また、注意事項として、 group by q10 でグループ化のキーとして、検索対象文字列の列のみ指定していますが、検索対象文字列に重複があり一意にならない場合は、カウントの集計が意図しないものになる(重複カウントしてしまう)ので、一意になるような複合キーで指定してください。

検索キーワードが全て含まれる検索対象文字列を抽出する

最後にmatch_resultが0のレコードを抽出して完了です。

select
    q10
from
    search_result
where
    match_result = 0
;

最後に

Amazon Athena上で、SQLのFilter句を使用してスペース区切りで入力された複数検索キーワードによる検索を実現する為のSQLクエリを解説しました。 用途が限定的な内容ですが、ニーズはあると思いますので、何処かで誰かの参考になれば幸いです。